<?php

namespace App\Http\Controllers\Admin\ExportList;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use App\Admin\SampleInfo;
use App\Http\Controllers\Admin\SampleInfoController;
use QrCode;


class PublicController extends Controller
{

    /* 试验台账 导出Excel */
    public function export($request, $search) {
        @set_time_limit(0);
        ini_set('max_execution_time', 0);
        ini_set('memory_limit', "-1");
        
        $para = $request->get('params');
        $para = json_decode($para, true);
        $para['is_search'] = $search;
        $sampleCon = new SampleInfoController();
        unset($para['length']); //导出数据不需要页码
        $data = $sampleCon->listData($para);
        if(is_string($data)) {
            return $data;
        }
        $total = $data['iTotalDisplayRecords'];
        if($total === 0) {
            return '内容为空';
        }
        $data = $data['data'];
        $template_file = './storage/ExcelTemplate/检测台账.xls'; //考虑到兼容Linux，斜杠用“/”
        if(!file_exists($template_file)) {
            return '模板文件不存在';
        }
        $setting = DB::table('setting')->find('1');
        $report_num_pre = $setting->report_num_pre; //报告编号前缀
        $report_num_pre = '';
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); //读取模板文件
        $worksheet = $spreadsheet->getSheet(0); //按索引获取表单，从 0 开始
        $worksheet->getCell("A1")->setValue($setting->name_rep.'--样品检测台账');
        $rowIndex_sam_info = 3;  //样品信息的行号
        $qr_code_path = [];
        $cols = [
            '序号'=>'no.',
            '送检单位'=>'b_client_name',
            '委托单位'=>'b_entrusted_company',
            '报告编号'=>['bef'=>$report_num_pre,'col'=>'b_batchNum'],
            '样品号'=>'bar_code',
            '样品自编号'=>'self_num',
            '样品名称'=>'c_tool_name',
            '规格型号'=>'guige_xinghao',
            '额定电压（kV）'=>'c_voltage',
            '分类'=>'c_class',
            '数量'=>['value'=>1],
            '单位'=>'c_unit',
            '检测项目'=>'c_item_names',
            '依据标准'=>'c_std_num',
            '试验日期'=>'test_time',
            '检测周期'=>['col'=>'i_cycle','after'=>'年'],
            '有效期'=>'next_time',
            '检测结果'=>'res',
            '不合格项目'=>'g_name',
            '不合格照片'=>'non_photo',
            '检测费用（元）'=>'money',
            '支付状态'=>'b_paid_status',
            '支付时间'=>'b_paid_at',
            '付款合同编号'=>'b_paid_num',
            '报告批准日期'=>'h_approve_end',
            '要求完成日期'=>'b_deadline',
            '报告发放日期'=>'h_sendback_end',
            '联系人'=>'b_client_contact',
            '联系电话'=>'b_client_tel',
            '备注'=>'c_remark',
        ];
        //设置E列（样品编号）的列宽
        $worksheet->getColumnDimension('E')->setWidth(11);
        //设置标题
        $colNo = 0;
        foreach ($cols as $title => $col) {
            $colNo += 1;
            $worksheet->setCellValueByColumnAndRow($colNo,'2',$title);
        }
        $worksheet->mergeCellsByColumnAndRow(1,1,$colNo,1); //第一行标题，合并单元格
        $styleArray = [
            'colors' => [
                'allColors' => [
                    'colorStyle' => \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLUE,
                ]
            ], 
        ];
        $worksheet->getStyleByColumnAndRow(1,2,$colNo,2)->applyFromArray($styleArray);
        
        //赋值
        foreach($data as $kk => $vv) {
            $time = strtotime($vv->ctime);
            $month = date('m', $time);
            $day = date('d', $time);
            $colNo = 0;
            foreach ($cols as $title => $col) {
                $colNo += 1;
                $workCol = dec2ABC($colNo);
                if(is_array($col)){
                    $vvCol = data_get($col,'col','');
                    $vvVal = data_get($vv,$vvCol,'');
                    switch($vvCol){
                        case 'bar_code':
                            $vvVal = substr($vvVal, -4, 3).'#';
                            break;
                        case 'res':
                            $vvVal = $vvVal === 1 ? '合格' : ($vvVal === 0 ? '未检测' : '不合格');
                            break;
                        case 'b_paid_status':
                            $vvVal = $vvVal == 1 ? '已支付' : '未支付';
                            break;
                        case 'non_photo':
                            /* ---------------- 不合格照片 -------------------- */
                                if(isset($request->export_nonphoto)) {
                                    $vv->non_photo = '.'.$vv->non_photo;
                                    if(is_file($vv->non_photo)) {
                                        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                                        $drawing->setPath($vv->non_photo); //图片的路径
                                        $worksheet->getCell($workCol.$rowIndex_sam_info);
                                        $drawing->setResizeProportional(false);                    //不保持宽高比
                                        $drawing->setWidthAndHeight(1.3*75, 75); //高度
                                        $drawing->setOffsetX(5);
                                        $drawing->setOffsetY(5);
                                        $drawing->setCoordinates($workCol.$rowIndex_sam_info); //指定位置,不合格照片
                                        $drawing->setWorksheet($worksheet);
                                    }
                                }
                                /* ---------------- 不合格照片end -------------------- */
                            break;
                        default:
                            break;
                    }
                    $value = data_get($col,'bef','').data_get($col,'value','').$vvVal.data_get($col,'after','');
                }else{
                    $value = $col == 'no.' ? ($kk + 1) : data_get($vv,$col,''); 
                }
                $worksheet->getCell($workCol.$rowIndex_sam_info)->setValue($value);
            }
            $worksheet->getRowDimension($rowIndex_sam_info)->setRowHeight(-1); //行高
            $rowIndex_sam_info ++;
        }
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ]
            ], 
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER, 
                'wrapText' => TRUE,
            ],
        ];
        $worksheet->getStyleByColumnAndRow(1,1,$colNo,($rowIndex_sam_info-1))->applyFromArray($styleArray);
        //----------直接在浏览器下载----start
        $today = date('Y-m-d');
        $outname = '近半年数据，下载时间：'.$today;
        if(isset($para['searcho'])) {
            $parao = array_column($para['searcho'], 'value', 'name');
            $danwei = $parao['b_entrusted_company'] ? '检测单位：'.$parao['b_entrusted_company'].'，' : '';
            $pici = $parao['b_batchNum'] ? '批次号：'.$parao['b_batchNum'].'，' : '';
            $startdate = $parao['test_time_0'];
            $enddate = $parao['test_time_1'];
            $date = '';
            if($startdate || $enddate) {
                $date .= '日期范围：';
                $date .= $enddate ? ($startdate ? $startdate : '').'至'.$enddate : ($startdate ? $startdate.'至'.$today : '');
                $date .= ',';
            }
            $outname = $danwei.$pici.$date.'下载时间：'.$today;
        }

        $file_name = "检测台账(".$outname.").xls";  //输出的文件名称
        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出Excel 07文件
        header('Content-Type: application/vnd.ms-excel');  //告诉浏览器输出Excel 03版本文件
        header('Content-Disposition: attachment;filename='.urlencode($file_name)); //告诉浏览器输出的文件名称
        header('Cache-Control: max-age=0'); //禁止缓存
        //----------直接在浏览器下载----end

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->setPreCalculateFormulas(false); //禁用公式预先计算
        $writer->save('php://output');           //直接在浏览器下载
        //为了防止内存泄露，建议用完手动清理
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        foreach($qr_code_path as $vv) {
            @unlink($vv);
        }
    }

}
